package cn.cytong.nk0713.services;

import cn.cytong.nk0713.utils.CreditUtil;
import cn.cytong.nk0713.utils.OperationLogUtil;
import cn.cytong.nk0713.utils.OperatorServiceLogWriter;
import cn.cytong.nk0713.utils.nutz.CardDataUtil;
import cn.cytong.nk0713.utils.nutz.Constants;
import cn.cytong.nk0713.utils.nutz.CustomisedNutDao;
import cn.cytong.rpc.server.NutzRpcService;
import cn.cytong.util.CardTool;
import cn.cytong.util.DataTypeTool;
import cn.cytong.util.DateTool;
import cn.cytong.util.StringTool;
import flexjson.JSONSerializer;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.joda.time.DateTime;
import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.ConnCallback;
import org.nutz.dao.Dao;
import org.nutz.dao.entity.Record;
import org.nutz.dao.util.cri.SimpleCriteria;
import org.nutz.trans.Atom;
import org.nutz.trans.Trans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

import static cn.cytong.nk0713.utils.nutz.Constants.*;
import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkNotNull;
import static org.apache.commons.lang3.Validate.*;

/**
 * 旅游卡信息
 */
public class CardService extends NutzRpcService {
    private long sequenceId = -1;
    private String defaultProductId = "1"; // 默认为年卡
    private String defaultPlatformType = "3"; // 默认为翼支付
    private String defaultPlatform = "bestpay"; // 默认为翼支付
    private String defaultCount = "1"; // 默认购卡数量
    private static SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    private static SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static SimpleDateFormat sequenceFormat = new SimpleDateFormat("yyyyMMdd");
    private static Logger logger = LoggerFactory.getLogger(CardService.class);
    private CustomisedNutDao nutDao;

    public String getProductInfo(Map params) {
        Map rtn = new HashMap();
        rtn.put("success", false);

        String productId = (String) params.get("productId");
        if (!DataTypeTool.isInteger(productId)) {
            productId = defaultProductId;
        }
        try {
            Record product = nutDao.fetchTableRecord("Product", "id = ?", Long.parseLong(productId));

            if (product != null) {
                rtn.put("name", product.getString("name"));
                rtn.put("price", product.getInt("price"));
                rtn.put("introduction", product.getString("introduction"));

                rtn.put("success", true);
            } else {
                rtn.put("msg", "未找到对应的产品信息！");
            }
        } catch (Exception e) {
            logger.error("error in method getYoungCardInfo", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 订单保存
     *
     * @param params orderFee 单位分
     * @return
     */
    public String saveTrade(Map params) {
        logger.info("saveTrade begin");
        Map<String, Object> rtn = new HashMap();
        try {
            // 获取传入参数
            String accessKey = (String) params.get("accessKey");
            String tradeNo = (String) params.get("outOrderNo"); // 支持传入商户订单号
            String outTradeNo = (String) params.get("orderNo");
            String outTradeAt = (String) params.get("orderTime");
            String tradeType = (String) params.get("orderType");
            String totalFee = (String) params.get("orderFee");
            String customerName = (String) params.get("customerName");
            String customerMobile = (String) params.get("customerMobile");
            String cardNo = (String) params.get("cardNo");
            String cardId = (String) params.get("cardId");
            String productId = (String) params.get("productId");
            String platform = (String) params.get("platform"); // 购卡用
            String count = (String) params.get("count"); // 购卡数量
            String platformType = (String) params.get("platformType"); // 续期用

            // 传入数据完整性检测
            isTrue(StringTool.isNotEmpty(accessKey), "accessKey 不能为空");
            isTrue(StringTool.isNotEmpty(outTradeNo), "orderNo 不能为空");
            isTrue(StringTool.isNotEmpty(outTradeAt), "orderTime 不能为空");
            isTrue(StringTool.isNotEmpty(tradeType), "orderType 不能为空");
            isTrue(StringTool.isNotEmpty(totalFee), "orderFee 不能为空");
            // 传入数据类型检测
            isTrue(DataTypeTool.isDouble(totalFee), "传入的订单金额有误");
            // 数据处理
            if (!DataTypeTool.isInteger(productId)) productId = defaultProductId;
            if (!DataTypeTool.isInteger(platformType)) platformType = defaultPlatformType;
            if (StringTool.isEmpty(platform)) platform = defaultPlatform;
            if (!DataTypeTool.isInteger(count)) count = defaultCount;

            // 单独处理城市服务的platform
            if ("E8D1D5E2B18A1F120C47EE75DABAEE894E413151".equals(accessKey)) {
                platform = "alipay_citiesService";
            }

            // 修复翼支付订单金额
            logger.info("orderNo=" + outTradeNo);
            logger.info("accessKey=" + accessKey);
            logger.info("totalFee old=" + totalFee);
            if ("YfEJ14dqJfkDfZbtcpU98-IKtNGzZL5Sj7zwc4tJ".equals(accessKey)) {
                BigDecimal decimal = new BigDecimal(totalFee);
                if (decimal.intValue() == 200) {
                    logger.info("200 -> 20000");
                    totalFee = "20000";
                }
            }
            logger.info("totalFee new=" + totalFee);

            // 获取操作员对象
            Record operator = nutDao.fetchTableRecord("Operator", "accessKey=?", accessKey);
            notNull(operator, "权限获取失败，未找到对应的操作员");
            Record card = null;
            Long operatorId = Long.parseLong(operator.getString("id"));
            int type = Integer.valueOf(tradeType).intValue();
            // 获取产品对象
            Record product = nutDao.fetchTableRecord("Product", "id=?", Long.valueOf(productId));
            notNull(product, "未找到对应的商品");
            // 获取其他数据
            String productName = product.getString("name");
            BigDecimal productPrice = BigDecimal.valueOf(product.getInt("price"));
            BigDecimal productMoney = new BigDecimal(totalFee);
            // 如果是续期，则找到要续期的卡
            if (type == Constants.Order_Type_Renewal) {
                logger.info("saveTrade renewal");
                if (StringTool.isEmpty(cardNo)) {
                    if (StringTool.isNotEmpty(cardId)) {
                        card = nutDao.fetchTableRecord("Card", "cardId=?", cardId.trim());
                    }
                } else {
                    card = nutDao.fetchTableRecord("Card", "cardNo=?", cardNo.trim());
                }
                notNull(card, "未查到需要续期的卡片");

                // 检查订单
                Record trade = nutDao.fetchTableRecord("H_WHNK_ORDER", "outTradeNo=?", outTradeNo);
                if (trade == null) {
                    // 创建新订单
                    if (StringTool.isEmpty(tradeNo)) {
                        tradeNo = makeTradeNo();
                    }
                    Date now = new Date();
                    Chain chain = Chain.make("name", customerName)
                            .add("product_id", Long.valueOf(productId)) // 订单商品ID
                            .add("operator_id", operatorId) // 操作员ID
                            .add("code", card.getString("cardNo"))
                            .add("card_id", card.getString("id")) // 操作员ID
                            .add("count", Integer.parseInt(count)) // 商品数量
                            .add("price", productPrice.intValue()) // 商品价格
                            .add("money", productMoney.intValue()) // 付款金额
                            .add("type", Integer.parseInt(platformType)) // 订单类型：1-银联，2-淘宝，3-翼支付
                            .add("orderNo", tradeNo) // 订单号
                            .add("outTradeNo", outTradeNo) // 外部订单号（翼支付、支付宝等等的订单号）
                            .add("outTradeAt", DateTool.valueOf(outTradeAt)) // 外部订单时间
                            .add("mobile", customerMobile) // 用户手机号
                            .add("orderAt", now) // 时间
                            .add("status", 2); // 0-等待付款，1-付款成功，2-续费完成，3-续期完成

                    nutDao.insert("H_WHNK_ORDER", chain);
                }

                // 返回数据
                rtn.put("tradeNo", trade.get("orderNo"));
                rtn.put("orderNo", outTradeNo);
                rtn.put("success", true);
            } else if (type == Constants.Order_Type_Buy) {
                logger.info("saveTrade buy");
                // 查询订单是否存在
                String securityCode = "";
                Record trade = nutDao.fetchTableRecord("PayOrder", "outTradeNo=? and payState=1", outTradeNo);


                if (trade == null) {
                    if (StringTool.isEmpty(tradeNo)) {
                        tradeNo = makeTradeNo();
                    }

                    Date now = new Date();
                    Chain chain = Chain.make("subject", productName)
                            .add("product_id", Long.valueOf(productId)) // 订单商品ID
                            .add("operator_id", operatorId) // 操作员ID
                            .add("count", Integer.parseInt(count)) // 商品数量
                            .add("price", productPrice.intValue()) // 商品价格
                            .add("money", productMoney.intValue()) // 付款金额
                            .add("platform", platform) // 订单来源（1：翼支付）
                            .add("orderId", tradeNo) // 订单号
                            .add("outTradeNo", outTradeNo) // 外部订单号（翼支付、支付宝等等的订单号）
                            .add("outTradeAt", DateTool.valueOf(outTradeAt)) // 外部订单时间
                            .add("orderType", tradeType) // 订单类型（1：购买；2：续期）
                            .add("payAt", now) // 订单时间
                            .add("createAt", now) // 订单时间
                            .add("name", customerName) // 用户姓名
                            .add("mobile", customerMobile) // 用户手机号
                            .add("payState", 1) // 订单状态（0：待付款；1：已付款；2：已发货；3：交易成功；4：交易关闭；5：退款中）
                            .add("securityCode", securityCode); // 激活码、购卡验证码
                    // 记录卡片信息
                    if (card != null) {
                        chain = chain.add("cardNo", cardNo);
                        chain = chain.add("card_id", Long.valueOf(card.getString("id")));
                    }
                    nutDao.insert("PayOrder", chain);
                    trade = nutDao.fetchTableRecord("PayOrder", "outTradeNo=? and operator_id=?", outTradeNo, operatorId);

                    logger.info("PayOrder insert");

                    // 创建取卡信息
                    if (productMoney.intValue() > 15000 || productMoney.intValue() == 200) { // 支付金额大于150元
                        securityCode = createSecurityCode(nutDao, Integer.parseInt(count), operatorId, trade, product);
                    }
                } else {
                    logger.info("order exist");

                    securityCode = trade.getString("securityCode");
                    if (StringTool.isEmpty(securityCode)) {
                        // 重建取卡信息
                        if (trade.getInt("money") > 15000 || trade.getInt("money") == 200) {
                            securityCode = createSecurityCode(nutDao, Integer.parseInt(count), operatorId, trade, product);
                        }
                    }
                }
                rtn.put("activeCode", securityCode);

                // 返回数据
                if (trade != null) {
                    logger.info("trade.id=" + trade.getInt("id"));
                    rtn.put("tradeNo", trade.get("orderId"));
                }
                rtn.put("orderNo", outTradeNo);
                rtn.put("success", true);
            }
        } catch (Exception e) {
            logger.error("error in method saveTrade", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        }

        logger.info("saveTrade end");

        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 创建订单对应的取卡码
     *
     * @param dao
     * @param count
     * @param operatorId
     * @param trade
     * @param product
     * @return
     */
    public String createSecurityCode(CustomisedNutDao dao, int count, long operatorId, Record trade, Record product) {
        String securityCode = "";

        logger.info("PickupCode create");
        Long orderId = Long.parseLong(trade.getString("id"));

        // 查询已存在的取卡码
        List<Record> list = nutDao.queryTableRecord("PickupCode", "order_id = ?", orderId);
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Record record = list.get(i);
                String code = record.getString("code");
                if (securityCode.length() > 0) {
                    securityCode += ",";
                }
                securityCode += code;
            }
        }
        if (list == null || list.size() < count) {
            if (list != null) count = count - list.size();
            for (int i = 0; i < count; i++) {
                String code = makeSecurityCode(nutDao);
                Chain chain = Chain.make("code", code)
                        .add("insertAt", new Date())
                        .add("state", 0)
                        .add("operator_id", operatorId)
                        .add("order_id", Long.parseLong(trade.getString("id")));
                nutDao.insert("PickupCode", chain);
                if (securityCode.length() > 0) {
                    securityCode += ",";
                }
                securityCode += code;
            }

            // 更新取卡码显示
            nutDao.executeUpdate("update PayOrder set securityCode = ? where id = " + trade.getString("id"), securityCode);

            // 销售量累计
            int volume = product.getInt("salesVolume");
            nutDao.executeUpdate("update Product set salesVolume = ? where id = " + product.getString("id"), volume + count);
        }

        return securityCode;
    }

    /**
     * 根据卡号或物理Id获取卡片信息
     *
     * @param params cardNo 卡号
     *               cardId 物理Id
     * @return
     */
    public String getCardInfo(Map params) {
        Map rtn = new HashMap();
        String cardNo = (String) params.get("cardNo");
        String cardId = (String) params.get("cardId");
        if (StringUtils.isNotBlank(cardNo)) {
            cardNo = cardNo.replaceAll("\\s", "");
        }

        logger.info("getCardInfo cardId({}) cardNo({})", cardId, cardNo);
        try {
            SimpleCriteria cri = Cnd.cri();
            if (StringUtils.isNotBlank(cardId)) cri.where().andEquals("cardId", cardId);
            if (StringUtils.isNotBlank(cardNo)) cri.where().andEquals("cardNo", cardNo);
            Record card = nutDao.fetchRecordWithCriteria("select * from card $condition", cri);

            notNull(card, "卡号不存在");

            Map cardMap = new HashMap();
            cardMap.put("isActive", card.get("isActive"));
            cardMap.put("cardNo", card.getString("cardNo"));
            cardMap.put("cardState", getStateText(card.getInt("state")));
            cardMap.put("state", card.get("state"));
            cardMap.put("activeType", card.get("activeType") != null ? card.get("activeType") : 1);
            cardMap.put("productType", card.get("productType"));
            cardMap.put("startAt", getDateString(card.get("startAt")));
            cardMap.put("endAt", getDateString(card.get("endAt")));

            // 是否可以续期
            if (CardTool.canRenewal(card)) {
                cardMap.put("canRenewal", 1);
            } else {
                cardMap.put("canRenewal", 0);
            }
            rtn.put("card", cardMap);

            Object customerId = card.get("customer_id");
            if (customerId != null) {
                Record customer = nutDao.fetchTableRecord("customer", "id = ?", customerId);

                if (customer != null) {
                    Map customerMap = new HashMap();

                    customerMap.put("customer", true);
                    customerMap.put("name", customer.get("name"));
                    customerMap.put("idNo", customer.get("idNo"));
                    rtn.put("customer", customerMap);
                }
            }

            rtn.put("success", true);
        } catch (Exception e) {
            logger.error("error in method getCardInfo", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 获取单张卡片信息
     *
     * @param params id           卡片Id
     *               cardId       卡片物理Id
     *               cardNo       卡号
     *               customerId   卡片绑定用户Id
     * @return card             卡片信息
     * card.customer    卡片绑定用户信息
     * success          true or false
     */
    public String getCard(Map params) {
        Map result = new HashMap();
        Object id = params.get("id");
        String cardId = (String) params.get("cardId");
        String cardNo = (String) params.get("cardNo");
        Object customerId = params.get("customerId");
        try {
            Record card = null;
            if (id != null)
                card = nutDao.fetchTableRecord("card", "id = ?", id);
            else if (StringUtils.isNotBlank(cardId))
                card = nutDao.fetchTableRecord("card", "cardId = ?", cardId);
            else if (StringUtils.isNotBlank(cardNo))
                card = nutDao.fetchTableRecord("card", "cardNo = ?", cardNo);
            else if (customerId != null)
                card = nutDao.fetchTableRecord("card", "customer_id = ?", customerId);
            checkNotNull(card, "未找到卡片信息");
            if (card.get("customer_id") != null) {
                Record customer = nutDao.fetchTableRecord("customer", "id = ?", card.get("customer_id"));
                card.put("customer", customer);
            }
            // 是否可以续期
            if (CardTool.canRenewal(card)) {
                card.put("canRenewal", 1);
            } else {
                card.put("canRenewal", 0);
            }
            card.put("cardState", getStateText(card.getInt("state")));
            card.put("startAt", getDateString(card.get("startAt")));
            card.put("endAt", getDateString(card.get("endAt")));
            card.put("activeType", card.get("activeType") != null ? card.get("activeType") : 1);
            result.put("card", card);
            result.put("success", true);
        } catch (Exception e) {
            logger.error("error in method CardService.getCardByCustomer", e);
            result.put("success", false);
            result.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(result);
    }

    /**
     * 工行联名卡入库
     *
     * @param params
     * @return
     */
    public String addNewCard(Map params) {
        Map result = new HashMap();
        final String cardId = (String) params.get("cardId");
        String operatorName = (String) params.get("operatorUsername");
        Object operatorId = params.get("operatorId");
        try {
            Record operator = dao.fetch("operator", Cnd.where("username", "=", operatorName));
            if (operator == null && operatorId != null)
                operator = dao.fetch("operator", Cnd.where("id", "=", operatorId));

            final Cnd cnd = Cnd.where("cardId", "=", cardId);
            Record card = dao.fetch("whnkCardDb.dbo.CardData", cnd);
            checkNotNull(card, "卡信息不存在");
            final String cardNo = card.getString("cardNo");
            card = dao.fetch("card", cnd);

            if (card != null) {
                checkArgument(card.getInt("isActive") == 0, "该卡已激活");
                if (!cardNo.equals(card.getString("cardNo")) || card.getInt("cardType") != 2)
                    dao.update("card", Chain.make("cardNo", cardNo).add("cardType", 2), cnd);
            } else {
                final Record finalOperator = operator;
                Trans.exec(new Atom() {
                    public void run() {
                        //add new card
                        dao.insert("card", Chain.make("cardId", cardId).add("cardNo", cardNo).add("insertAt", new Date())
                                .add("ver", 1).add("productType", 0).add("cardType", 2).add("state", 0).add("isActive", false)
                                .add("verifyType", 1).add("yearCode", "2014"));
                        //add new card log
                        Record newCard = dao.fetch("card", cnd);
                        OperatorServiceLogWriter.saveOperatorLog(null, finalOperator, newCard,
                                Constants.service_type_add_new_card, null, dao);
                    }
                });
            }
            result.put("success", true);
            result.put("cardNo", cardNo);
        } catch (Exception e) {
            logger.error("error in method CardService.addNewCard", e);
            result.put("success", false);
            result.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(result);
    }

    /**
     * 通过用户信息获取用户绑定卡片
     *
     * @param params customerId  用户Id
     * @return card        卡片信息
     * success     true or false
     */
    public String getCardByCustomer(Map params) {
        Map result = new HashMap();
        Object customerId = params.get("customerId");
        try {
            Record card = nutDao.fetchRecord("select * from card where state = 1 and customer_id = ?", customerId);
            if (card == null) {
                throw new Exception("未找到卡片信息");
            }
            result.put("card", card);
            result.put("success", true);
        } catch (Exception e) {
            logger.error("error in method CardService.getCardByCustomer", e);
            result.put("success", false);
            result.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(result);
    }

    public String getCardData(Map params) {
        Map rtn = new HashMap();
        try {
            String cardId = (String) params.get("cardId");
            String cardNo = (String) params.get("cardNo");
            String oldCardId = (String) params.get("oldCardId");
            Long customerId = Long.valueOf((String) params.get("customerId"));
            Long operatorId = Long.parseLong(params.get("operatorId").toString());

            isTrue(cardNo.length() == 16, "身份证号长度错误！");
            isTrue(StringUtils.isNotEmpty(cardNo), "卡号不能为空！");
            isTrue(StringUtils.isNotEmpty(cardId), "卡ID不能为空！");
            cardNo = cardNo.replaceAll(" ", "");    //清除空格

            Record customer = nutDao.fetchTableRecord("Customer", "id = ?", customerId);
            notNull(customer, "未找到用户信息！");

            Record card = nutDao.fetchTableRecord("Card", "cardId = ?", cardId);

            isTrue(card != null, "卡片未激活，请先激活！");
            if (!cardNo.equals(card.get("cardNo"))) {
                isTrue(nutDao.fetchLong("select count(*) card where cardNo = ?", cardNo) < 1, "卡号已存在，请重新核对！");
            }

            card.put("cardNo", cardNo);
            card.put("customer_id", customer.get("id"));
            card.put("operator_id", operatorId);

            if (oldCardId != null) {
                Record c = nutDao.fetchTableRecord("Card", "cardId = ?", oldCardId);
                if (c != null) {
                    card.put("startAt", c.get("startAt"));
                    card.put("endAt", c.get("endAt"));
                }
            }
            CardDataUtil cardDataUtil = new CardDataUtil();
            Map data = cardDataUtil.getAllData(customer, card, nutDao);
            rtn.put("data", data);

            Map keys = cardDataUtil.getSectorKey(cardId);
            rtn.put("keys", keys);

            rtn.put("success", true);
        } catch (Exception e) {
            logger.error("error in method getCardData", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 激活年卡，使用卡号激活，可以输入起始卡号和数量
     *
     * @param params startCardNo -- 起始卡号
     *               cardId -- 物理Id
     *               operatorUsername -- 2016-9-19，操作员用户名，优先使用用户名查找操作员
     *               operatorId -- 操作员Id
     *               activeType -- 激活类型 1 售卡，其他 补卡
     *               note -- 丽红总店操作员ID
     *               startAt -- 起始日期，yyyy-MM-dd
     *               endAt -- 截止日期，yyyy-MM-dd
     * @return
     */
    public String activateCard(Map params) {
        Map rtn = new HashMap();
        rtn.put("success", false);
        try {
            String cardId = (String) params.get("cardId");
            String startCardNo = (String) params.get("startCardNo");
            notEmpty(startCardNo, "激活卡号不能为空！");
            startCardNo = startCardNo.replaceAll("\\s", "");
            final String note = (String) params.get("note"); // 丽红总店操作员ID
            String startAt = (String) params.get("startAt");
            String endAt = (String) params.get("endAt");

            Integer activeCount = ((Integer) params.get("activeCount"));
            if (activeCount == null) activeCount = 1;
            isTrue(activeCount >= 1, "卡片激活数量至少为1张 ");

            String endCardNo = String.format("%016d", Long.parseLong(startCardNo) + activeCount - 1);

            // 用户ID
            Long customerId = null;
            if(params.containsKey("customerId")) customerId = Long.parseLong(params.get("customerId").toString());

            Integer operatorId = (Integer) params.get("operatorId");
            String operatorUsername = (String) params.get("operatorUsername");
            final Integer activeType = Integer.parseInt(params.get("activeType").toString());

            Record operator = null;
            if (StringUtils.isNotBlank(operatorUsername))
                operator = nutDao.fetchTableRecord("operator", "username = ?", operatorUsername);
            if (operator == null && operatorId != null)
                operator = nutDao.fetchTableRecord("operator", "id = ?", operatorId);

            notNull(operator, "未找到操作员信息：username（%s），id（%s）", operatorUsername, operatorId);
            operatorId = operator.getInt("id");
            notNull(operator.get("serviceProvider_id"), "未找到操作员的服务商信息！");
            notNull(operator.get("serviceBranch_id"), "未找到操作员的网点信息！");

            Cnd cnd = (Cnd) Cnd.where("cardNo", ">=", startCardNo)
                    .and("cardNo", "<=", endCardNo)
                    .and("len(cardNo)", "=", 16)
                    .and("state", "=", "0").and("isActive", "=", "0").asc("cardNo");
            final List<Record> cardList = dao.query("Card", cnd, dao.createPager(1, activeCount));
            isTrue(activeCount == cardList.size(), "激活数量错误，未激活卡片数量为" + cardList.size());

            isTrue(cardList.size() != 0, "卡片未入库或已激活，无法激活！");
//            Long operatorId = Long.valueOf(operator.getString("id"));

            //  卡片的起止日期
            Date startDate = new DateTime().withTimeAtStartOfDay().toDate();
            Date endDate = DateUtils.addDays(startDate, 364);
            if (StringUtils.isNotBlank(startAt)) {
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                startDate = dateFormat.parse(startAt);
                endDate = dateFormat.parse(endAt);
            }

            final List<Chain> listChain = new ArrayList<Chain>();
            for (Record card : cardList) {
                String cardNo = card.getString("cardNo");
                isTrue(isValidCardNo(cardNo, operator), "您无权激活卡片(%s)！", cardNo.replaceAll("\\d{4}(?!$)", "$0 "));

                if (StringUtils.isNotBlank(cardId)) {
                    isTrue(cardId.equals(card.get("cardId").toString()), "卡号(%s)的物理ID(%s)和读取到的物理ID(%s)不一致！",
                            cardNo, card.get("cardId").toString(), cardId);
                }

                Chain chain = Chain.make("isActive", Boolean.TRUE)
                        .add("activeType", activeType)
                        .add("operator_id", operatorId)
                        .add("activeAt", new Date())
                        .add("startAt", startDate)
                        .add("endAt", endDate);

                // 绑定用户（补卡）
                if(customerId != null) chain.add("customer_id", customerId);

                listChain.add(chain);
            }

            final Record finalOperator = operator;
            Trans.exec(new Atom() {
                public void run() {
                    for (int i = 0; i < cardList.size(); i++) {
                        dao.update("card", listChain.get(i), Cnd.where("id", "=", cardList.get(i).get("id")));
                        Double price = CreditUtil.getPriceFromCard(cardList.get(i), nutDao);
                        if (activeType == 1) {  //  售卡激活

                            // 扣除信用额度（通过抛出异常回滚事务）
                            int credit = price.intValue();
                            int deductType = CreditUtil.deductCredit(finalOperator, credit, nutDao);
                            String msg = "[Cards.activate] 扣除信用额度 " + credit + OperationLogUtil.getBalanceInfo(finalOperator, nutDao);
                            switch (deductType) {
                                case CreditUtil.TYPE_OPERATOR:
                                    OperationLogUtil.info(finalOperator, OperationLogUtil.Operation_Type_Active, msg, null, null, finalOperator, credit, nutDao);
                                    break;
                                case CreditUtil.TYPE_BRANCH:
                                    Record branch = nutDao.fetchRecord("select * from serviceBranch where id = ?", finalOperator.get("serviceBranch_id"));
                                    OperationLogUtil.info(finalOperator, OperationLogUtil.Operation_Type_Active, msg, null, branch, null, credit, nutDao);
                                    break;
                                case CreditUtil.TYPE_PROVIDER:
                                    Record provider = nutDao.fetchRecord("select * from serviceProvider where id = ?", finalOperator.get("serviceProvider_id"));
                                    OperationLogUtil.info(finalOperator, OperationLogUtil.Operation_Type_Active, msg, provider, null, null, credit, nutDao);
                                    break;
                            }

                            OperatorServiceLogWriter.saveOperatorLog(note, finalOperator, cardList.get(i), null,
                                    Constants.service_type_activate_new, new BigDecimal(price), deductType, dao);
                        } else {    //  补卡激活，费用10元
                            OperatorServiceLogWriter.saveOperatorLog(note, finalOperator, cardList.get(i),
                                    Constants.service_type_activate_reissue, Constants.REISSUE_CARD_FEE, dao);
                        }
                    }
                }
            });
            rtn.put("success", true);
        } catch (Exception e) {
            logger.error("error in method activateCard", e);
            rtn.put("success", false);
            rtn.put("msg", e != null ? e.getMessage() : "未知错误！");
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 返回信用额
     * @param params cardId
     *               cardNo
     * @return
     */
    public String recoverCredit(Map params) {
        Map rtn = new HashMap();
        try {
            Trans.begin();
            String cardId = (String) params.get("cardId");
            String cardNo = (String) params.get("cardNo");

            //  find card
            SimpleCriteria cri = Cnd.cri();
            if (StringUtils.isNotBlank(cardId)) cri.where().andEquals("cardId", cardId);
            if (StringUtils.isNotBlank(cardNo)) cri.where().andEquals("cardNo", cardNo);
            final Record card = nutDao.fetchRecordWithCriteria("select * from Card $condition", cri);
            notNull(card, "未找到卡片信息");/*
            isTrue(((Boolean) card.get("isActive")), "卡片未激活");
            isTrue(card.getInt("state") == 0, "卡片状态错误");*/

            //  find OperatorServiceLog
            cri = Cnd.cri();
            cri.where().andEquals("card_id", card.get("id"))
                    .andEquals("serviceType", 10);
            cri.getOrderBy().desc("id");
            final Record log = nutDao.fetchRecordWithCriteria("select * from OperatorServiceLog $condition", cri);
            notNull(log, "未找到激活记录");
            isTrue(log.getInt("provider_id") != 4, "暂不支持工行操作员取消操作");

            final Record operator = nutDao.fetchRecord("select * from Operator where id = ?", log.get("operator_id"));
            int credit = ((BigDecimal) log.get("fee")).intValue();
            int recoverType = CreditUtil.recoverCredit(card, nutDao);
            String msg = "[Cards.inactive] 返回信用额度 " + credit + OperationLogUtil.getBalanceInfo(operator, nutDao);
            switch (recoverType) {
                case CreditUtil.TYPE_OPERATOR:
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, null, null, operator, credit, nutDao);
                    break;
                case CreditUtil.TYPE_BRANCH:
                    Record branch = nutDao.fetchRecord("select * from serviceBranch where id = ?", operator.get("serviceBranch_id"));
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, null, branch, null, credit, nutDao);
                    break;
                case CreditUtil.TYPE_PROVIDER:
                    Record provider = nutDao.fetchRecord("select * from serviceProvider where id = ?", operator.get("serviceProvider_id"));
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, provider, null, null, credit, nutDao);
                    break;
        }
            Trans.commit();
        }catch (Exception e) {
            try {
                Trans.rollback();
            } catch (Exception e1) {
            }
            logger.error("扣除信用额失败", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        } finally {
            try {
                Trans.close();
            } catch (Exception e) {
            }
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 年卡续期
     *
     * @param params cardNo -- 卡号
     *               cardId -- 物理Id
     *               operatorId -- 操作员Id
     *               endAt  --  有效期，续期时该字段有效
     * @return
     */
    public String cardRenewal(Map params) throws Exception {
        String cardNo = MapUtils.getString(params, "cardNo");
        String cardId = MapUtils.getString(params, "cardId");
        String seqNo = MapUtils.getString(params, "seqNo");
        String accessKey = MapUtils.getString(params, "accessKey");

        Map rtn = new HashMap();
        try {
            Long operatorId = MapUtils.getLong(params, "operatorId");

            if (operatorId == null) {
                // 获取操作员对象
                Record operator = nutDao.fetchTableRecord("Operator", "accessKey=?", accessKey);
                notNull(operator, "权限获取失败，未找到对应的操作员");
                operatorId = Long.valueOf(operator.getString("id"));
            }

            Trans.DEBUG = true;
            Trans.begin();

            // cardNo 和 cardId 都可续费已激活的卡
            Record card = null;
            if (StringTool.isNotEmpty(cardId)) {
                card = nutDao.fetchTableRecord("card", "cardId = ? and isActive=1", cardId);
            }
            if (card == null && StringTool.isNotEmpty(cardNo)) {
                card = nutDao.fetchTableRecord("card", "cardNo = ? and isActive=1", cardNo);
            }

            isTrue(card != null, "卡片不存在：cardId(%s), cardNo(%s)", cardId, cardNo);
            isTrue(card.getInt("customer_id") != -1, "卡片还未绑定用户，无法续期，请直接购买新卡！");

            // 是否可以续期
            Date endAt = (Date) card.get("endAt");
            Boolean isWhtCard = card.getInt("cardType") == 3;
            if (!isWhtCard) //  除了武汉通卡，其他卡的续期有时间要求
                isTrue(CardTool.canRenewal(endAt), "续期失败：到期前三个月内才能续期！\n年卡卡号：%s\n结束时间：%s",
                        cardNo, new DateTime(card.get("endAt")).toString("yyyy-MM-dd"));

            //工行操作员  需要流水号
            Record operator = nutDao.fetchTableRecord("operator", "id = ?", operatorId);

            //  计算新的有效期
            if (isWhtCard && params.get("endAt") != null) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                endAt = format.parse(params.get("endAt").toString());
            } else {
                endAt = CardTool.getRenewalEndDate(endAt);
            }

            int newState = 1;
            if (card.get("customer_id") == null) newState = 0;
            nutDao.executeUpdate("update card set endAt = ?, state = ?, isActive = ? where id = ?",
                    endAt, newState, true, card.get("id"));
            Double price = CreditUtil.getPriceFromCard(card, nutDao);
            // 扣除信用额度（通过抛出异常回滚事务）
            int credit = price.intValue();
            int deductType = CreditUtil.deductCredit(operator, credit, nutDao);
            String msg = "[Cards.activate] 扣除信用额度 " + credit + OperationLogUtil.getBalanceInfo(operator, nutDao);
            switch (deductType) {
                case CreditUtil.TYPE_OPERATOR:
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Active, msg, null, null, operator, credit, nutDao);
                    break;
                case CreditUtil.TYPE_BRANCH:
                    Record branch = nutDao.fetchRecord("select * from serviceBranch where id = ?", operator.get("serviceBranch_id"));
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Active, msg, null, branch, null, credit, nutDao);
                    break;
                case CreditUtil.TYPE_PROVIDER:
                    Record provider = nutDao.fetchRecord("select * from serviceProvider where id = ?", operator.get("serviceProvider_id"));
                    OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Active, msg, provider, null, null, credit, nutDao);
                    break;
            }


            //保存日志
            Record customer = nutDao.fetchTableRecord("customer", "id = ?", card.get("customer_id"));
            OperatorServiceLogWriter.saveOperatorLog(null, operator, card, customer, service_type_renewal_card, new BigDecimal(price), deductType, dao);

            String startAtString = new DateTime(card.get("startAt")).toString("yyyy-MM-dd");
            String endAtString = new DateTime(endAt).toString("yyyy-MM-dd");

            rtn.put("success", true);
            rtn.put("id", card.get("id"));
            rtn.put("startAt", startAtString);
            rtn.put("endAt", endAtString);
            rtn.put("msg", String.format("续期成功：请到景区自助机或售卡网点重新写卡！\n年卡卡号：%s\n开始时间：%s\n结束时间：%s\n", cardNo,
                    new DateTime(card.get("startAt")).toString("yyyy-MM-dd"), new DateTime(endAt).toString("yyyy-MM-dd")));
            Trans.commit();
        } catch (Exception e) {
            Trans.rollback();
            String msg = e.getMessage() != null ? e.getMessage() : e.toString();
            logger.error("error in method CardService.cardRenewal", e);
            rtn.put("success", false);
            rtn.put("msg", msg);
        } finally {
            Trans.close();
        }
        return new JSONSerializer().serialize(rtn);
    }

    /**
     * 取卡更新记录 更新卡箱卡片数量
     *
     * @param /count       取卡数量
     * @param /startCardNo 起始卡号
     * @param /operatorId  自助机操作员ID
     * @param /boxPosition 卡箱位置 1-左 2-中 3-右
     */
    public String takeCards(Map params) {
        Map rtn = new HashMap();
        try {
            Trans.begin();
            Integer count = (Integer) params.get("count");
            String startCardNo = (String) params.get("startCardNo");
            Object operatorId = params.get("operatorId");
            Integer boxPosition = (Integer) params.get("boxPosition");
            notNull(operatorId, "operatorId not null");
            notNull(startCardNo, "startCardNo not null");

            if (count == null || count <= 0) {
                count = 1;
            }
            Cnd cnd = Cnd.where("operator_id", "=", operatorId);
            if (boxPosition != null) {
                cnd.and("position", "=", boxPosition);
            }
            Record box = dao.fetch("cardBox", cnd);
            notNull(box, "卡箱信息不存在");
            Chain boxCh = Chain.make("cardCount", box.getInt("cardCount") - count)
                    .add("lastTakeAt", new Date());
            dao.update("cardBox", boxCh, cnd.where("id", "=", box.get("id")));

            Chain logCh = Chain.make("amount", count * -1)
                    .add("logAt", new Date())
                    .add("operator_id", operatorId)
                    .add("cardBox_id", box.get("id"))
                    .add("startCardNo", startCardNo)
                    .add("endCardNo", String.valueOf(Long.valueOf(startCardNo) + count - 1));
            dao.insert("cardBoxLog", logCh);
            rtn.put("success", true);
            Trans.commit();
        } catch (Exception e) {
            try {
                Trans.rollback();
            } catch (Exception e1) {
            }
            logger.error("卡箱信息更新失败", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        } finally {
            try {
                Trans.close();
            } catch (Exception e) {
            }
        }
        return new JSONSerializer().serialize(rtn);
    }

    //  取消激活
    public String reverseActivate(Map params) {
        Map rtn = new HashMap();

        try {
            String cardId = (String) params.get("cardId");
            String cardNo = (String) params.get("cardNo");

            //  find card
            SimpleCriteria cri = Cnd.cri();
            if (StringUtils.isNotBlank(cardId)) cri.where().andEquals("cardId", cardId);
            if (StringUtils.isNotBlank(cardNo)) cri.where().andEquals("cardNo", cardNo);
            final Record card = nutDao.fetchRecordWithCriteria("select * from Card $condition", cri);
            notNull(card, "未找到卡片信息");
            isTrue(((Boolean) card.get("isActive")), "卡片未激活");
            isTrue(card.getInt("state") == 0, "卡片状态错误");

            //  find OperatorServiceLog
            cri = Cnd.cri();
            cri.where().andEquals("card_id", card.get("id"))
                    .andEquals("serviceType", 10);
            cri.getOrderBy().desc("id");
            final Record log = nutDao.fetchRecordWithCriteria("select * from OperatorServiceLog $condition", cri);
            notNull(log, "未找到激活记录");
            isTrue(log.getInt("provider_id") != 4, "暂不支持工行操作员取消操作");

            final Record operator = nutDao.fetchRecord("select * from Operator where id = ?", log.get("operator_id"));
            Trans.exec(new Atom() {
                public void run() {
                    nutDao.getBaseDao().update("Card", Chain.make("isActive", 0)
                                    .add("startAt", null)
                                    .add("endAt", null),
                            Cnd.where("id", "=", card.get("id"))
                    );

                    // 扣除信用额度（通过抛出异常回滚事务）
                    int credit = ((BigDecimal) log.get("fee")).intValue();
                    int recoverType = CreditUtil.recoverCredit(card, nutDao);
                    String msg = "[Cards.inactive] 返回信用额度 " + credit + OperationLogUtil.getBalanceInfo(operator, nutDao);
                    switch (recoverType) {
                        case CreditUtil.TYPE_OPERATOR:
                            OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, null, null, operator, credit, nutDao);
                            break;
                        case CreditUtil.TYPE_BRANCH:
                            Record branch = nutDao.fetchRecord("select * from serviceBranch where id = ?", operator.get("serviceBranch_id"));
                            OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, null, branch, null, credit, nutDao);
                            break;
                        case CreditUtil.TYPE_PROVIDER:
                            Record provider = nutDao.fetchRecord("select * from serviceProvider where id = ?", operator.get("serviceProvider_id"));
                            OperationLogUtil.info(operator, OperationLogUtil.Operation_Type_Inactive, msg, provider, null, null, credit, nutDao);
                            break;
                    }

                    OperatorServiceLogWriter.saveOperatorLog(null, operator, card,
                            Constants.service_type_inactivate_for_sale,
                            ((BigDecimal) log.get("fee")).multiply(BigDecimal.valueOf(-1)),
                            nutDao.getBaseDao());
                }
            });

            rtn.put("success", true);
        } catch (Exception e) {
            logger.error("error in method CardService.reverseActivate", e);
            rtn.put("success", false);
            rtn.put("msg", e.getMessage());
        }

        return new JSONSerializer().serialize(rtn);
    }

    //  验证服务商是否有权限激活年卡
    public boolean isValidCardNo(String cardNo, Record operator) {

        checkNotNull(operator, "操作员信息不能为空！");
        checkNotNull(operator.get("serviceBranch_id"), "网点信息不能为空！");
        checkNotNull(operator.get("serviceProvider_id"), "服务商信息不能为空！");

        Long count = nutDao.fetchLong("select count(*) from ActivationLimit where " +
                        "(operator_id = ? or serviceBranch_id = ? or serviceProvider_id = ?)" +
                        " and startNo <= ? and endNo >= ? and status = 1",
                operator.get("id"), operator.get("serviceBranch_id"),
                operator.get("serviceProvider_id"), cardNo, cardNo);
        /*Long count = ActivationLimit.count("(operator.id = ? or serviceBranch.id = ? or serviceProvider.id = ?) " +
                        "and startNo <= ? and endNo >= ? and status = true", operator.id, operator.serviceBranch.id,
                operator.serviceProvider.id, cardNo, cardNo);*/

        return count > 0;

        /*List<Record> cardNosList = nutDao.queryTableRecord("ActivationLimit", "serviceProvider_id = ? and " +
                "startNo <= ? and endNo >= ?", providerId, cardNo, cardNo);
        if (cardNosList != null && cardNosList.size() > 0)
            return true;
        return false;*/
    }

    /**
     * 获取新的订单号（yyyyMM+10位数字）
     *
     * @return
     */
    public String makeTradeNo() {
        StringBuffer retval = new StringBuffer(sequenceFormat.format(new Date()));
        String seq = StringTool.toString(getSequence());
        int n = 10 - seq.length();
        String ran = StringTool.toRandomString(n);
        retval.append(seq + ran);
        return retval.toString();
    }


    /**
     * 生成6位数的提货码
     *
     * @return 提货码
     */
    private static String makeSecurityCode(CustomisedNutDao nutDao) {
        logger.info("makeSecurityCode begin");
        while (true) {
            String code = StringTool.toRandomString(6);
            logger.info("code=" + code);
            int count = nutDao.fetchLong("select count(*) from PickupCode where code=?", code).intValue();
            if (count == 0) {
                logger.info("makeSecurityCode end");
                return code;
            }
        }
    }

    /**
     * 获取唯一ID
     *
     * @return
     */
    private long getSequence() {
        Dao dao = nutDao.getBaseDao();
        dao.run(new ConnCallback() {
            public void invoke(Connection conn) throws Exception {
                String sqlString = "INSERT INTO Sequence(type) VALUES(1)";
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(sqlString, Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = stmt.getGeneratedKeys();
                if (rs.next()) sequenceId = rs.getLong(1);
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            }
        });
        return sequenceId;
    }

    public String getStateText(Integer state) {
        if (state == null) {
            return "未知";
        }
        switch (state) {
            case 0:
                return "库存";
            case 1:
                return "使用中";
            case 2:
                return "已挂失";
            case 3:
                return "已过期";
            case 4:
                return "已停用";
            case 5:
                return "已损坏";
            default:
                return "未知";
        }
    }

    public String getDateString(Object date) {
        if (date == null) {
            return "";
        } else {
            return dateFormat.format((Date) date);
        }
    }

}
